package is.bean.entity;

import java.sql.*;
import java.util.Collection;
import java.util.LinkedList;
import javax.ejb.*;

/**
 * For beans using 'Integer id' primary key
 *
 * @author aspr0
 */
public abstract class BeanRoutinesIntegerId extends BeanRoutines
{
    protected Integer id;

    /**
     * @see javax.ejb.EntityBean#ejbActivate()
     */
    public void ejbActivate()
    {
        id = (Integer) context.getPrimaryKey();
    }

    /**
     * @see javax.ejb.EntityBean#ejbPassivate()
     */
    public void ejbPassivate()
    {
        id = null;
    }

    /**
     * See EJB 2.0 and EJB 2.1 section 12.2.5
     */
    public Integer ejbFindByPrimaryKey(Integer primaryKey) throws SQLException, FinderException
    {
        Connection connection = null;
        PreparedStatement statement = null;

        try
        {
            connection = dataSource.getConnection();
            String sql = "select count(*) count from %s where id = ?";
            sql = String.format(sql, tableName);
            statement = connection.prepareStatement(sql);

            statement.setInt(1, primaryKey);
            ResultSet resultSet = statement.executeQuery();
            resultSet.next();

            Integer count = resultSet.getInt("count");

            if (count == 1)
            {
                return primaryKey;
            }
            else
            {
                throw new FinderException();
            }
        }
        finally
        {
            if (connection != null)
            {
                connection.close();
            }

            if (statement != null)
            {
                statement.close();
            }
        }
    }

    /**
     * Finds correct parents for hierarchy beans while updating. Prevents from cycling hierarchy tree.
     *
     * @param id
     * @return
     * @throws FinderException
     */
    public Collection ejbFindParentsAvailable(Integer id) throws FinderException
    {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection<Integer> foundIds = new LinkedList<Integer>();
        try
        {
            String sql = "select id "
                    + "from " + tableName + " "
                    + "where id not in ("
                    + "select id "
                    + "from " + tableName + " "
                    + "start with id = ? "
                    + "connect by prior id = parentId)";
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            while (resultSet.next())
            {
                foundIds.add(resultSet.getInt("id"));
            }
        }
        catch (SQLException e)
        {
            FinderException fe = new FinderException();
            fe.initCause(e);
            throw fe;
        }
        finally
        {
            try
            {
                if (resultSet != null)
                {
                    resultSet.close();
                }
                if (statement != null)
                {
                    statement.close();
                }
                if (connection != null)
                {
                    connection.close();
                }
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return foundIds;
    }

    /**
     * @see javax.ejb.EntityBean#ejbRemove()
     */
    public void ejbRemove() throws RemoveException
    {
        Connection connection = null;
        PreparedStatement statement = null;
        try
        {
            connection = dataSource.getConnection();
            String sql = "delete from " + tableName + " where id = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            statement.execute();
        }
        catch (SQLException e)
        {
            RemoveException re = new RemoveException();
            re.initCause(e);
            throw re;
        }
        finally
        {
            try
            {
                if (statement != null)
                {
                    statement.close();
                }
                if (connection != null)
                {
                    connection.close();
                }
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
    }

    /**
     * Similar to ejbFindByPrimaryKey but doesnt return entity
     *
     * @param primaryKey
     * @return true if entity exists
     * @throws SQLException
     * @throws FinderException
     */
    public boolean ejbHomeEntityExist(Integer primaryKey) throws SQLException
    {
        Connection connection = null;
        PreparedStatement statement = null;

        try
        {
            connection = dataSource.getConnection();
            String sql = "select count(*) count from %s where id = ?";
            sql = String.format(sql, tableName);
            statement = connection.prepareStatement(sql);

            statement.setInt(1, primaryKey);
            ResultSet resultSet = statement.executeQuery();
            resultSet.next();

            Integer count = resultSet.getInt("count");

            if (count == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        finally
        {
            if (connection != null)
            {
                connection.close();
            }

            if (statement != null)
            {
                statement.close();
            }
        }
    }

    /**
     * Selects all rows within range. First row is 1.
     *
     * @param start set -1 to select all
     * @param end
     * @return
     */
    public Collection ejbFindByRange(int start, int end) throws FinderException, SQLException
    {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection<Integer> foundIds = new LinkedList<Integer>();
        try
        {
            connection = dataSource.getConnection();
            String sql;

            if (start > 0)
            {
                sql =
                        "select id "
                        + "from"
                        + "("
                        + "select id, rownum rn "
                        + "from %s "
                        + "where rownum <= ? "
                        + "order by id"
                        + ")"
                        + "where rn >= ? ";
                sql = String.format(sql, tableName);

                statement = connection.prepareStatement(sql);

                statement.setInt(1, start);
                statement.setInt(2, end);
            }
            else
            {
                sql = String.format("select id from %s", tableName);
                statement = connection.prepareStatement(sql);
            }

            resultSet = statement.executeQuery();
            while (resultSet.next())
            {
                foundIds.add(resultSet.getInt("id"));
            }
        }
        catch (SQLException e)
        {
            FinderException fe = new FinderException();
            fe.initCause(e);
            throw fe;
        }
        finally
        {
            if (resultSet != null)
            {
                resultSet.close();
            }
            if (statement != null)
            {
                statement.close();
            }
            if (connection != null)
            {
                connection.close();
            }
        }
        return foundIds;
    }
}
